# -*- coding: utf-8 -*-
__author__ = 'sunnychou'
__date__ = '2019/9/20 14:03'

'''
销售情况分析表
功能：
   将月数据统计表进行合并，产生大数据统计表，写成excel文件，发送邮件
   销售情况分析表是从经营情况分析表中获取出不含毛利的字段组成的报表

销售情况分析表

表结构：
   销售情况分析表  ---  k3jyqks

计算时间点：
    月底（最后半天）计算本月所有数据

处理过程：
  1. 由月份获取当月的合并数据，返回dataframe数据集
  2. 将dataframe数据集直接写excel
  3. 发邮件(见common模块中的email_helper.py)

'''
# 导入依赖库
from common.pandas_helper import PandasHelper
from common.utils import FVDateTime, get_month, series_value, increase_rate
import pandas as pd
from common.logger_helper import g_wlogger
from common.email_helper import email_send_with_appendix_list
from common.mssql_helper import g_msql_inst
from k3_data_report.finance.utils import fetch_lastyear_ljqk

CHTJ_DICT = {
    'k3jyqks': "select   ymonth, scode, kehuname, depart, khname, kuanqi, paytype, mhetonge, ycke,  "
               "yske,  myysk, yfdqysk, yfcqys, yfykfp    from k3jyqks  where  paytype='%s' and ymonth  BETWEEN '%s' and '%s'",
    'k3_cpgly': "select   gys, cpgly   from k3_cpgly order by sjsj desc",
}


def generate_sql(type, begin_time, end_time, paytype='人民币'):
    target_tb_sql = CHTJ_DICT[type] % (paytype, begin_time, end_time)
    return target_tb_sql


def create_df_xsqkfx(month=None, paytype='人民币'):
    '''
    客户经营情况报表
    :param month: 输入的月份，默认是本月
    :return:
    '''
    # 查询品牌，产品经理，产品助理，当月出库额，时间范围为：当年上月，去年同月，当年当前季度，去年同季度
    if month == None:
        month = get_month(FVDateTime().to_dict()['this_month_start'])

    the_year = int(month.split("-")[0])
    fvdate_dict = FVDateTime(month).to_dict()

    # 本年度数据，获取所有的从今年1月份到现在本月的时间数据
    this_year_start = fvdate_dict['this_year_start']
    this_month_end = fvdate_dict['this_month_end']

    target_tb_sql = generate_sql("k3jyqks", this_year_start, this_month_end, paytype)
    target_full_df = PandasHelper.pd_query_sql(target_tb_sql)
    if target_full_df.empty:
        g_wlogger.werror("create_df_k3jyqks:target_full_df, all data is empty.")
        return pd.DataFrame()

    # 待计算数值字段进行转换
    # target_full_df['mhetonge','monthml','ycke','ymaoli','yske', 'skyml','myysk','yfdqysk','yfcqys','yfykfp'] = pd.to_numeric(target_full_df['mhetonge','monthml','ycke',
    #                                             'ymaoli','yske','skyml','myysk','yfdqysk','yfcqys','yfykfp'])
    target_full_df[['mhetonge',  'ycke', 'yske',  'myysk', 'yfdqysk', 'yfcqys', 'yfykfp']] = \
    target_full_df[
        ['mhetonge',  'ycke',  'yske',  'myysk', 'yfdqysk', 'yfcqys', 'yfykfp']].apply(
        pd.to_numeric)

    # 今年本月
    this_month = get_month(this_month_end)
    # 查询本年度本月的
    month_cond = f'ymonth==["{this_month}"]'
    this_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    # 判断this_year_this_month_df是否能查询到，不为空，分组计算，否则为None
    # bydde,  byrke,byyfk, byyinfk,  byysp, wsfp, xyyjyfk
    this_year_this_month_group_df = this_year_this_month_df.groupby('scode')[
        'mhetonge',  'ycke',  'yske',  'myysk',
        'yfdqysk', 'yfcqys', 'yfykfp'].sum() if not this_year_this_month_df.empty else pd.DataFrame()

    # 本年到目前为止的金额统计
    # 查询本年度本月的
    this_year_start_month = get_month(this_year_start)
    month_cond = (target_full_df['ymonth'] >= this_year_start_month) & (
            target_full_df['ymonth'] <= this_month)
    this_year_this_month_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    # bydde,  byrke,byyfk, byyinfk,  byysp, wsfp, xyyjyfk
    this_year_group_df = this_year_this_month_df.groupby('scode')[
        'mhetonge',  'ycke',  'yske',  'myysk',
        'yfdqysk', 'yfcqys', 'yfykfp'].sum() if not this_year_this_month_df.empty else pd.DataFrame()

    # 上年累计欠款
    lastyear_qk_dict = fetch_lastyear_ljqk()

    # 获取基础信息
    # scode, kehuname, depart, khname, kuanqi, paytype
    target_chtjcp_base_df = target_full_df[
        ['scode', 'kehuname', 'depart', 'khname', 'kuanqi', 'paytype']].drop_duplicates()  # 获取待分析的品牌，产品经理，产品助理
    target_code_series = target_full_df['scode'].drop_duplicates()  # 获取唯一参考
    df_data_list = []
    for scode in target_code_series:
        base_df = target_chtjcp_base_df.query(f'scode==["{scode}"]')
        kehuname = base_df['kehuname'].values[0]
        depart = base_df['depart'].values[0]
        khname = base_df['khname'].values[0]
        kuanqi = base_df['kuanqi'].values[0]
        paytype = base_df['paytype'].values[0]
        # 获取本年本月合同额
        this_year_this_month_mhetonge = 0 if this_year_this_month_group_df.empty else \
        this_year_this_month_group_df['mhetonge'].get(scode,0)
        this_year_mhetonge = 0 if this_year_group_df.empty else this_year_group_df['mhetonge'].get(scode,0)


        # 获取本月出库额
        this_year_this_month_ycke = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['ycke'].get(scode,0)
        this_year_ycke = 0 if this_year_group_df.empty else this_year_group_df['ycke'].get(scode,0)


        # 获取本月收款额
        this_year_this_month_yske = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['yske'].get(scode,0)
        this_year_yske = 0 if this_year_group_df.empty else this_year_group_df['yske'].get(scode,0)


        # 获取本月应收款
        this_year_this_month_myysk = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['myysk'].get(scode,0)
        this_year_myysk = 0 if this_year_group_df.empty else this_year_group_df['myysk'].get(scode,0)

        # 获取到期应收款
        this_year_this_month_yfdqysk = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['yfdqysk'].get(scode,0)
        this_year_yfdqysk = 0 if this_year_group_df.empty else this_year_group_df['yfdqysk'].get(scode,0)

        # 获取超期应收款
        this_year_this_month_yfcqys = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['yfcqys'].get(scode,0)
        this_year_yfcqys = 0 if this_year_group_df.empty else this_year_group_df['yfcqys'].get(scode,0)

        # 应开发票
        this_year_this_month_yfykfp = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['yfykfp'].get(scode,0)
        this_year_yfykfp = 0 if this_year_group_df.empty else this_year_group_df['yfykfp'].get(scode,0)

        # 上年末未收款,期初未收款
        last_year_total_wsk = lastyear_qk_dict.get(scode, 0)
        if last_year_total_wsk != 0:
            last_year_total_wsk = last_year_total_wsk[1] if the_year == 2019 else last_year_total_wsk[0]

        # 期末累计未收款 = 期初未收款 +累计出库 - 本年累计已收款
        year_qmljwsk = "%.2f" %  (float(last_year_total_wsk) + this_year_ycke - this_year_yske)
        #year_qmljwsk = "%.2f" %  (float(last_year_total_wsk) + this_year_ycke - this_year_yske)
        # TODO 银行承兑票
        kh_yhcdpje = 0

        # 如果一条记录中所有的年度累计金额都为空，则跳过
        if (this_year_mhetonge == 0) and (this_year_yske == 0) and (this_year_myysk == 0) and (
                this_year_yfcqys == 0) and (this_year_yfykfp == 0):
            continue
        # 'scode','kehuname', 'depart', 'khname', 'kuanqi', 'paytype'
        item_tuple = (scode, kehuname, depart, khname, kuanqi, last_year_total_wsk,
                      this_year_mhetonge,  this_year_this_month_mhetonge, this_year_ycke,
                      this_year_this_month_ycke, year_qmljwsk, kh_yhcdpje, this_year_yske,  this_year_this_month_yske,
                      this_year_this_month_myysk, this_year_this_month_yfdqysk, this_year_this_month_yfcqys, this_year_this_month_yfykfp)
        df_data_list.append(item_tuple)

    # 说明：此处的columns列一定要和上述item_tuple顺序对应上
    columns = ["序号", "客户名称", "部门", "客户经理", "款期", "2018年年未收款",
               "本年累计有效合同额",  "本月合同额",  "本年累计出库额",
               "本月出库额",  "期末累计未收款", "银行承兑汇票", "累计已收款", "本月收款额",
                "本月应收款", "下月到期应收", "下月超期应收", "下月应开发票"]

    k3jyqks_df = PandasHelper.create_dataframe(data=df_data_list, columns=columns)

    return k3jyqks_df


def create_df_xsqkfx_rmb(month, paytype="人民币"):
    k3jyqks_rmb_df = create_df_xsqkfx(month, paytype=paytype)
    if k3jyqks_rmb_df.empty:
        g_wlogger.werror(f"k3xsqkfx_dp_main:create_df_xsqkfx_rmb {paytype} return empty, please check.")
    return k3jyqks_rmb_df


def create_df_xsqkfx_mj(month, paytype="美金"):
    k3jyqks_mj_df = create_df_xsqkfx(month, paytype=paytype)
    if k3jyqks_mj_df.empty:
        g_wlogger.werror(f"k3xsqkfx_dp_main:create_df_xsqkfx_mj {paytype} return empty, please check.")
    return k3jyqks_mj_df


def k3xsqkfx_dp_main(month):
    # 1. 由月份获取当月的合并数据，返回dataframe数据集
    # 人民币
    paytype = '人民币'
    k3jyqks_rmb_df = create_df_xsqkfx(month, paytype=paytype)
    if k3jyqks_rmb_df.empty:
        g_wlogger.werror(f"k3xsqkfx_dp_main:create_df_xsqkfx {paytype} return empty, please check.")
        return

    # 2. 写excel
    PandasHelper.df_to_excel(k3jyqks_rmb_df, f"xsqkfx_{paytype}.xls")

    paytype = '美金'
    k3jyqks_rmb_df = create_df_xsqkfx(month, paytype=paytype)
    if k3jyqks_rmb_df.empty:
        g_wlogger.werror("k3xsqkfx_dp_main:create_df_xsqkfx {paytype}  return empty, please check.")
        return
    # 2. 写excel
    PandasHelper.df_to_excel(k3jyqks_rmb_df, f"xsqkfx_{paytype}.xls")

    # 3. 发送邮件
    subject = f"财务统计报表-{month}"  # 邮件主题
    mail_msg = "财务统计报表-销售情况报表"  # 邮件主体部分内容，可以写成html格式
    appendix_files = ["xsqkfx_人民币.xls", "xsqkfx_美金.xls"]
    email_send_with_appendix_list(subject, mail_msg, appendix_files)


if __name__ == "__main__":
    month = '2019-07'

    k3xsqkfx_dp_main(month)







